-------------------------------------------------------------
-- @name: gather_histogram
-- @author: dion cho
-- @description: test for gathering histogram
--------------------------------------------------------------

-- create objects
drop table t1 purge;
create table t1(c1 int, c2 int);

create index t1_n1 on t1(c1+1);

-- c1 = frequency
-- c2 = height-balanced
insert into t1
select 1, mod(level, 2)+1
from dual
connect by level <= 10000
union all
select 2, mod(level, 300)+1
from dual
connect by level <= 1000
union all
select 3, mod(level, 50)+1
from dual
connect by level <= 100
union all
select 4, mod(level, 5)+1
from dual
connect by level <= 10
union all
select 5, mod(level, 1)+1
from dual
connect by level <= 1
;

commit;

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all columns size 1');

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all columns size auto');
      
exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all columns size skewonly');
      
exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all indexed columns size skewonly');

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for columns c1 size 5 c2 size skewonly');

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all hidden columns size skewonly');

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for columns SYS_NC00003$ size skewonly');
